package csu.web.petStore.persistence.impl;

import csu.web.petStore.domain.Category;
import csu.web.petStore.domain.Product;
import csu.web.petStore.persistence.DBUtil;
import csu.web.petStore.persistence.ProductDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {

    private static final String getProductString=
            "SELECT PRODUCTID,NAME,DESCN as description ,CATEGORY as categoryId FROM PRODUCT WHERE PRODUCTID = ?";//别名
    private static final String getProductStringByCategoryString=
            "SELECT PRODUCTID,NAME,DESCN as description ,CATEGORY as categoryId FROM PRODUCT WHERE CATEGORY = ?";
    private static final String searchProductListString=
            "SELECT PRODUCTID,NAME,DESCN as description ,CATEGORY as categoryId FROM PRODUCT WHERE lower(name) like ?";
    public Product getProduct(String categoryId)
    {
        Product product=null;
        try
        {
            Connection connection= DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(getProductString);
            preparedStatement.setString(1,categoryId);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next())//有下一行
            {
                product=new Product();
                product.setProductId(resultSet.getString(1));
                product.setCategoryId(resultSet.getString(2));
                product.setName(resultSet.getString(3));
                product.setDescription(resultSet.getString(4));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return product;
    }


    public List<Product> getProductListByCategory(String categoryId)
    {
        List<Product> products=new ArrayList<Product>();
        try{
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(getProductStringByCategoryString);
            preparedStatement.setString(1,categoryId);
            ResultSet resultSet=preparedStatement.executeQuery();
            while(resultSet.next())
            {
                Product product=new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                products.add(product);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return products;
    }


   public List<Product> searchProductList(String keywords)
   {
       List<Product> products=new ArrayList<Product>();
       try{
           Connection connection=DBUtil.getConnection();
           PreparedStatement preparedStatement=connection.prepareStatement(searchProductListString);
           preparedStatement.setString(1,keywords);
           ResultSet resultSet=preparedStatement.executeQuery();
           while(resultSet.next())
           {
               Product product=new Product();
               product.setProductId(resultSet.getString(1));
               product.setName(resultSet.getString(2));
               product.setDescription(resultSet.getString(3));
               product.setCategoryId(resultSet.getString(4));
               products.add(product);
           }
           DBUtil.closeResultSet(resultSet);
           DBUtil.closePreparedStatement(preparedStatement);
           DBUtil.closeConnection(connection);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return products;
   }
}
